﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyDoanVien.General;
using System.Data;
using System.Data.SqlClient;
using QuanLyDoanVien.Bussiness;


namespace QuanLyDoanVien.DataLayer
{
    class KyLuatData
    {
        DataService m_KyLuatData = new DataService();
        public List<KyLuatInfo> layDSKyLuat()
        {
            List<KyLuatInfo> rs = new List<KyLuatInfo>();
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Tab_KyLuat");
            m_KyLuatData.Load(cmd);
            foreach(DataRow dr in m_KyLuatData.Rows){
                KyLuatInfo kl = new KyLuatInfo();
                kl.MaKyLuat = dr["MaKyLuat"].ToString();
                kl.TenKyLuat = dr["TenKyLuat"].ToString();
                kl.GhiChu = dr["GhiChu"].ToString();
                rs.Add(kl);
            }
            m_KyLuatData.CloseConnection();
            return rs;
        }
  

        #region Them 
        public bool LuuKyLuat()
        {
            return m_KyLuatData.ExecuteNoneQuery() > 0;
        }

        public void ThemKyLuat(KyLuatInfo kl)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO Tab_KyLuat VALUES(@MaKyLuat, @TenKyLuat,@GhiChu)");
            cmd.Parameters.Add("MaKyLuat", SqlDbType.Char).Value = kl.MaKyLuat;
            cmd.Parameters.Add("TenKyLuat", SqlDbType.NVarChar).Value = kl.TenKyLuat;
            cmd.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = kl.GhiChu;
            m_KyLuatData.Load(cmd);
        }
        #endregion

        #region Sua
        public bool SuaKyLuat(KyLuatInfo kl)
        {
            SqlCommand cmd = new SqlCommand("UPDATE Tab_KyLuat SET TenKyLuat = @TenKyLuat, GhiChu = @GhiChu WHERE MaKyLuat = @MaKyLuat");
            cmd.Parameters.Add("MaKyLuat", SqlDbType.Char).Value = kl.MaKyLuat;
            cmd.Parameters.Add("TenKyLuat", SqlDbType.NVarChar).Value = kl.TenKyLuat;
            cmd.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = kl.GhiChu;
            m_KyLuatData.Load(cmd);
            try
            {
                m_KyLuatData.ExecuteNoneQuery(cmd);
                m_KyLuatData.CloseConnection();
                return true;
            }
            catch
            {
                m_KyLuatData.CloseConnection();
                return false;
            }
        }
        #endregion

        #region Xoa 
        public bool XoaKyLuat(String makyluat)
        {
            SqlCommand cmd = new SqlCommand("DELETE FROM Tab_KyLuat WHERE MaKyLuat = @MaKyLuat");
            cmd.Parameters.Add("MaKyLuat", SqlDbType.Char).Value = makyluat;
            m_KyLuatData.Load(cmd);
            try
            {
                m_KyLuatData.ExecuteNoneQuery(cmd);
                m_KyLuatData.CloseConnection();
                return true;
            }
            catch
            {
                m_KyLuatData.CloseConnection();
                return false;
            }
        }

        #endregion

        public string getAutoID()
        {
            DataService.OpenConnection();
            QuyDinh qd = new QuyDinh();
            string id = "";
            string sql = "select top 1 MaKyLuat from Tab_KyLuat Order By MaKyLuat desc";
            SqlCommand command = new SqlCommand(sql);
            m_KyLuatData.Load(command);
            if (m_KyLuatData.Rows.Count < 1)
                return "KL00000001";
            id = m_KyLuatData.Rows[0][0].ToString();
            int num = int.Parse(id.Substring(2, 8)) + 1;
            id = "LK" + qd.LaySTT(num);
            m_KyLuatData.CloseConnection();
            return id;
        }
    }
}
